﻿//using System;
//using Microsoft.VisualStudio.TestTools.UnitTesting;
//using System.Data;

//namespace Framework.UnitTest
//{
//    [TestClass]
//    public class ExcleExportTest
//    {
	
//        [TestMethod]
//        public void 调查统计结果()
//        {
//            string conn = "Data Source=221.209.110.83;Initial Catalog=diaocha;uid=hospital;pwd=!QAZ@WSX;Pooling=False";
//            DataSet ds = Framework.Common.Sql.SqlHelper.ExecuteDataset(conn,
//                   System.Data.CommandType.Text,
//                   @"
//DECLARE @resulttable table (部门 nvarchar(500),SupWWID nvarchar(500),姓名 nvarchar(500),职位 nvarchar(500),城市 nvarchar(500),下属数量2012 INT,已答题下属数量2012 INT ,总分2012 INT,平均分2012 INT,自评分数2012 INT,
//已答题下属数量2011 INT ,总分2011 INT,平均分2011 INT,自评分数2011 INT,自评19 INT,总分19 INT,下属19平均分  INT,提高 int)
//
//DECLARE @suptable table(dept nvarchar(500),SupWWID nvarchar(500),SupName nvarchar(500),supPOSITION NVARCHAR(200),supcity NVARCHAR(200),id INT IDENTITY)
//INSERT INTO @suptable 
//SELECT DISTINCT Dept,ISNULL(supwwid,0) AS supwwid,SupName,SupPosition,SupCity
//FROM dbo.Staff s
//ORDER BY Dept ASC
//
//DECLARE @id INT 
//DECLARE @supnanme NVARCHAR(500)
//DECLARE @dept NVARCHAR(500)
//DECLARE @supwwid NVARCHAR(500)
//DECLARE @POSITION NVARCHAR(200)
//DECLARE @city NVARCHAR(200)
//
//
//DECLARE @xiashucount INT
//DECLARE @xiashudaticount INT
//DECLARE @zongfen INT
//DECLARE @pingjunfen INT
//DECLARE @zipingfen INT
//
//DECLARE @已答题下属数量2011 INT 
//DECLARE @总分2011 INT
//DECLARE @平均分2011 INT
//DECLARE @自评分数2011 INT
//
//DECLARE @自评19 INT
//DECLARE @总分19 INT
//DECLARE @下属19平均分  int
//
//WHILE(EXISTS(SELECT TOP 1 1 FROM @suptable))
//BEGIN
//SELECT TOP 1 @supwwid= SupWWID,@id = id,@supnanme=SupName,@dept=dept,@city = SupCity,@POSITION=supPOSITION FROM @suptable
//
//SELECT @xiashucount = COUNT(*) FROM dbo.Staff WHERE SupWWID=@supwwid
//
//SELECT @xiashudaticount = COUNT(*) FROM dbo.Staff a JOIN dbo.StaffScore b ON a.WWID = b.userid
//WHERE a.SupWWID=@supwwid AND paperid=1
//
//SELECT @zongfen = SUM(c.score) FROM dbo.Staff a JOIN dbo.StaffScoreCheckDetail b ON a.WWID = b.userid
//JOIN dbo.Answer c ON b.answerid = c.answerid AND b.questionid = c.questionid
//JOIN dbo.Question q ON c.questionid = q.questionid
//WHERE b.paperid=1 AND a.SupWWID=@supwwid AND qtype='radio' AND q.qorder<19
//
//SELECT @zipingfen = SUM(c.score) FROM dbo.StaffScoreCheckDetail b 
//JOIN dbo.Answer c ON b.answerid = c.answerid AND b.questionid = c.questionid
//JOIN dbo.Question q ON c.questionid = q.questionid
//WHERE b.paperid=2 AND b.userid=@supwwid AND qtype='radio' AND q.qorder<19
//
//SELECT @pingjunfen = @zongfen/@xiashudaticount
//
//SELECT @已答题下属数量2011 = COUNT(*) FROM (
//SELECT DISTINCT 工号 FROM dbo.[2011xiashu]
//WHERE 主管编码=@supwwid) t
//
//SELECT @总分2011 = SUM(score) FROM dbo.[2011xiashu]
//WHERE 主管编码=@supwwid
//
//SELECT @平均分2011 = @总分2011/@已答题下属数量2011
//
//SELECT @自评分数2011=SUM(score) FROM dbo.[2011zhuguan]
//WHERE 工号=@supwwid
//
//
//
//
//
//
//
//
//
//
//SELECT @总分19 = SUM(c.score) FROM dbo.Staff a JOIN dbo.StaffScoreCheckDetail b ON a.WWID = b.userid
//JOIN dbo.Answer c ON b.answerid = c.answerid AND b.questionid = c.questionid
//JOIN dbo.Question q ON c.questionid = q.questionid
//WHERE b.paperid=1 AND a.SupWWID=@supwwid AND qtype='radio' AND q.qorder=19
//
//SELECT @自评19 = SUM(c.score) FROM dbo.StaffScoreCheckDetail b 
//JOIN dbo.Answer c ON b.answerid = c.answerid AND b.questionid = c.questionid
//JOIN dbo.Question q ON c.questionid = q.questionid
//WHERE b.paperid=2 AND b.userid=@supwwid AND qtype='radio' AND q.qorder=19
//
//SELECT @下属19平均分 = @总分19/@xiashucount
//
//
//
//
//
//INSERT INTO @resulttable
//        ( 部门 ,
//          SupWWID ,
//          姓名 ,
//		  职位,
//		  城市,
//          下属数量2012 ,
//          已答题下属数量2012 ,
//          总分2012 ,
//          平均分2012 ,
//          自评分数2012,
//		  已答题下属数量2011  ,总分2011 ,平均分2011 ,自评分数2011,
//		  自评19,总分19,下属19平均分,提高
//        )
//VALUES  ( @dept,@supwwid,@supnanme,@POSITION,@city,@xiashucount,@xiashudaticount,@zongfen,@pingjunfen,@zipingfen,
//@已答题下属数量2011,@总分2011,@平均分2011,@自评分数2011,@自评19,@总分19,@下属19平均分,@pingjunfen-@平均分2011
//)
//
//DELETE FROM @suptable WHERE id=@id
//END
//
//drop TABLE dbo.result
//
//SELECT * 
//INTO result
//FROM @resulttable
//
//select * from result
//");

//            var excel = new Framework.ExcelExport.ExcelHelper();
//            excel.DataTableToExcel(ds.Tables[0]);
//            excel.SaveFile(@"c:\调查结果" + DateTime.Now.ToString("MMdd") + ".xls");
//        }

//        [TestMethod]
//        public void 调查各组前百分之三十()
//        {
//            string conn = "Data Source=221.209.110.83;Initial Catalog=diaocha;uid=hospital;pwd=!QAZ@WSX;Pooling=False";
//            string[] depts = new string[] { "AD",
//"Admin 行政部 ",
//"Biz Strategy & Dev",
//"CD & MA",
//"CNS Marketing",
//"CNS Sales",
//"COE",
//"Commercial HQ",
//"Commercial Sales",
//"Discovery Center",
//"Distrib. Ser. 配送部",
//"EHS",
//"EM/EHS",
//"Equipment Management",
//"Facility",
//"Finance 财务部 ",
//"GA & PDD",
//"GCO",
//"IT 资讯管理部 ",
//"Logistics",
//"ME & QM",
//"MS Marketing",
//"MS Sales",
//"Non-Solids 非固体 ",
//"NPI",
//"OTC MKT",
//"OTC Retail Sales",
//"PD",
//"PDMS",
//"Planning&Purchasing",
//"Plant Mgt 工厂管理",
//"PPI",
//"PR 公关部 ",
//"PRA&GS",
//"Procurement 采购部 ",
//"Prod.Maint 生产维修",
//"Prod.Mgt 生产管理",
//"Production Support",
//"QA",
//"QC",
//"SAP COE",
//"Quality & Compliance",
//"Registration 注册部 ",
//"SFE&Sales Operation",
//"Solids 固体",
//"TS",
//"Validation",
//"Warehouse & PPC",
//"XJP University"};
//            foreach (var d in depts)
//            {
//                DataSet ds = Framework.Common.Sql.SqlHelper.ExecuteDataset(conn,
//                       System.Data.CommandType.Text,
//                       @"  SELECT TOP 30 PERCENT  * FROM result where 部门 ='" + d + @"'
//  ORDER BY 平均分2012 DESC");

//                var excel = new Framework.ExcelExport.ExcelHelper();
//                excel.DataTableToExcel(ds.Tables[0]);
//                excel.SaveFile(@"c:\平均分前百分之三十" + d + DateTime.Now.ToString("MMdd") + ".xls");

//                ds = Framework.Common.Sql.SqlHelper.ExecuteDataset(conn,
//       System.Data.CommandType.Text,
//       @"  SELECT TOP 30 PERCENT  * FROM result where 部门 ='" + d + @"'
//  ORDER BY 提高 DESC");

//                excel = new Framework.ExcelExport.ExcelHelper();
//                excel.DataTableToExcel(ds.Tables[0]);
//                excel.SaveFile(@"c:\改进率前百分之三十" + d + DateTime.Now.ToString("MMdd") + ".xls");
//            }
//        }


//        [TestMethod]
//        public void 调查完成情况()
//        {
//            string conn = "Data Source=221.209.110.83;Initial Catalog=diaocha;uid=hospital;pwd=!QAZ@WSX;Pooling=False";
//            DataSet ds = Framework.Common.Sql.SqlHelper.ExecuteDataset(conn,
//                   System.Data.CommandType.Text,
//                   @"
//SELECT  a.*,
//'是否完成下属调查'= (SELECT TOP 1 '完成' FROM dbo.StaffScore WHERE userid=a.wwid AND paperid=1),
//'是否完成自评调查'= (SELECT TOP 1 '完成' FROM dbo.StaffScore WHERE userid=a.supwwid AND paperid=2)
//FROM    dbo.Staff a 
//ORDER BY Dept
//");

//            var excel = new Framework.ExcelExport.ExcelHelper();
//            excel.DataTableToExcel(ds.Tables[0]);
//            excel.SaveFile(@"c:\完成情况" + DateTime.Now.ToString("MMdd") + ".xls");
//        }

//        [TestMethod]
//        public void 调查原始数据()
//        {
//            string conn = "Data Source=221.209.110.83;Initial Catalog=diaocha;uid=hospital;pwd=!QAZ@WSX;Pooling=False";
//            DataSet ds = Framework.Common.Sql.SqlHelper.ExecuteDataset(conn,
//                   System.Data.CommandType.Text,
//                   @"
//SELECT  CASE ( dbo.Question.paperid )
//          WHEN 1 THEN '下属问卷'
//          WHEN 2 THEN '自评问卷'
//        END AS '问卷类型',
//        '部门' = staff.Dept,
//        '主管编号' = staff.SupWWID,
//        '主管姓名' = staff.SupName,
//        dbo.StaffScoreCheckDetail.userid AS '员工编号' ,
//        dbo.staff.NameCN AS '员工姓名',
//        dbo.Question.qorder '问题编号',
//        dbo.Question.qtitle AS '问题' ,
//	dbo.Answer.score AS '问题分值',
//        dbo.Answer.atitle ,
//        dbo.Answer.ytitle ,
//        dbo.Answer.xtitle
//FROM    dbo.Question
//        JOIN dbo.Answer ON dbo.Question.questionid = dbo.Answer.questionid
//        JOIN dbo.StaffScoreCheckDetail ON dbo.Answer.questionid = dbo.StaffScoreCheckDetail.questionid
//                                          AND dbo.Answer.answerid = dbo.StaffScoreCheckDetail.answerid
//		JOIN staff ON WWID = StaffScoreCheckDetail.userid
//where dbo.Question.paperid = 1 
//ORDER BY 
//        staff.Dept,
//        dbo.Question.paperid ,
//        userid ,
//        dbo.Question.questionid ,
//        dbo.Question.qorder
//");

//            var excel = new Framework.ExcelExport.ExcelHelper();
//            excel.DataTableToExcel(ds.Tables[0]);


//            ds = Framework.Common.Sql.SqlHelper.ExecuteDataset(conn,
//                   System.Data.CommandType.Text,
//                   @"
//SELECT  CASE ( dbo.Question.paperid )
//          WHEN 1 THEN '下属问卷'
//          WHEN 2 THEN '自评问卷'
//        END AS '问卷类型',
//        '部门' = t.Dept,
//        dbo.StaffScoreCheckDetail.userid AS '员工编号' ,
//        t.SupName AS '员工姓名',
//        dbo.Question.qorder '问题编号',
//        dbo.Question.qtitle AS '问题' ,
//	dbo.Answer.score AS '问题分值',
//        dbo.Answer.atitle ,
//        dbo.Answer.ytitle ,
//        dbo.Answer.xtitle
//FROM    dbo.Question
//        JOIN dbo.Answer ON dbo.Question.questionid = dbo.Answer.questionid
//        JOIN dbo.StaffScoreCheckDetail ON dbo.Answer.questionid = dbo.StaffScoreCheckDetail.questionid
//                                          AND dbo.Answer.answerid = dbo.StaffScoreCheckDetail.answerid
//		JOIN (select distinct SupName,SupWWID,Dept from staff) t ON t.SupWWID = StaffScoreCheckDetail.userid
//where dbo.Question.paperid = 2 
//ORDER BY
//        t.Dept,
//        dbo.Question.paperid ,
//        userid ,
//        dbo.Question.questionid ,
//        dbo.Question.qorder
//");
//            excel.ChangeCurrentWorkSheet(2);
//            excel.DataTableToExcel(ds.Tables[0]);
//            excel.SaveFile(@"c:\原始数据" + DateTime.Now.ToString("MMdd") + ".xls");
//        }

//        [TestMethod]
//        public void 调查原始数据问答题()
//        {
//            string conn = "Data Source=221.209.110.83;Initial Catalog=diaocha;uid=hospital;pwd=!QAZ@WSX;Pooling=False";
//            DataSet ds = Framework.Common.Sql.SqlHelper.ExecuteDataset(conn,
//                   System.Data.CommandType.Text,
//                   @"
//SELECT  CASE ( dbo.Question.paperid )
//          WHEN 1 THEN '下属问卷'
//          WHEN 2 THEN '自评问卷'
//        END AS '问卷类型',
//        '部门' = staff.Dept,
//        '主管编号' = staff.SupWWID,
//        '主管姓名' = staff.SupName,
//        dbo.StaffScoreInputDetail.userid AS '员工编号' ,
//        dbo.staff.NameCN AS '员工姓名',
//        dbo.Question.qorder '问题编号',
//        dbo.Question.qtitle AS '问题' ,
//        dbo.StaffScoreInputDetail.answer AS '回答'
//FROM    dbo.Question
//        JOIN dbo.StaffScoreInputDetail ON dbo.Question.questionid = dbo.StaffScoreinputDetail.questionid
//		JOIN staff ON WWID = StaffScoreinputdetail.userid
//where dbo.Question.paperid = 1 
//ORDER BY dbo.Question.paperid ,
//        userid ,
//        dbo.Question.questionid ,
//        dbo.Question.qorder
//");

//            var excel = new Framework.ExcelExport.ExcelHelper();
//            excel.DataTableToExcel(ds.Tables[0]);


//            ds = Framework.Common.Sql.SqlHelper.ExecuteDataset(conn,
//                   System.Data.CommandType.Text,
//                   @"
//SELECT  CASE ( dbo.Question.paperid )
//          WHEN 1 THEN '下属问卷'
//          WHEN 2 THEN '自评问卷'
//        END AS '问卷类型',
//        '部门'=t.dept,
//        dbo.StaffScoreInputDetail.userid AS '员工编号' ,
//        t.SupName AS '员工姓名',
//        dbo.Question.qorder '问题编号',
//        dbo.Question.qtitle AS '问题' ,
//        dbo.StaffScoreInputDetail.answer AS '回答'
//FROM    dbo.Question
//        JOIN dbo.StaffScoreInputDetail ON dbo.Question.questionid = dbo.StaffScoreinputDetail.questionid
//		JOIN
//		(SELECT DISTINCT dept, SupName,SupWWID FROM staff) t
//		  ON t.SupWWID = StaffScoreinputdetail.userid
//where dbo.Question.paperid = 2 
//ORDER BY dbo.Question.paperid ,
//        userid ,
//        dbo.Question.questionid ,
//        dbo.Question.qorder
//");
//            excel.ChangeCurrentWorkSheet(2);
//            excel.DataTableToExcel(ds.Tables[0]);
//            excel.SaveFile(@"c:\调查原始数据问答题" + DateTime.Now.ToString("MMdd") + ".xls");
//        }

//        [TestMethod]
//        public void 新飞狐交易记录()
//        {
//            string conn = "server=10.0.251.188;initial catalog=DB_VirtualStock;user id=mystock;pwd=1qaz!%)!#*;persist security info=False; Max Pool Size=300";
//            DataSet ds = Framework.Common.Sql.SqlHelper.ExecuteDataset(conn,
//                   System.Data.CommandType.Text,
//                   @"
//SELECT  t.*,h.*
//FROM    ( SELECT    userID ,
//                    [historyComment] ,
//                    historyID ,
//                    historyType
//          FROM      [VirtualInvestment_Stock_userHistory] h WITH ( NOLOCK )
//          WHERE     contestID = 1
//                    AND historyComment <> ''
//                    AND historyDateTime <= '" + DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") + @"'
//        ) h
//        RIGHT JOIN ( SELECT * ,
//                            userid = ISNULL(( SELECT TOP 1
//                                                        userid
//                                              FROM      [dbo].VirtualInvestment_Stock_HeXunJoinUserList
//                                              WHERE     hexunUserName = a.新飞狐注册账号 AND userType=0
//                                            ), 0)
//                     FROM   dbo.Export_XingYeStock a
//                   ) t ON h.userID = t.userid
//");

//            var excel = new Framework.ExcelExport.ExcelHelper();
//            excel.DataTableToExcel(ds.Tables[0]);
//            excel.SaveFile(@"c:\新飞狐交易记录" + DateTime.Now.ToString("MMdd") + ".xls");
//        }


//        [TestMethod]
//        public void 新飞狐投顾周收益率()
//        {
//            string conn = "server=10.0.251.188;initial catalog=DB_VirtualStock;user id=mystock;pwd=1qaz!%)!#*;persist security info=False; Max Pool Size=300";
//            DataSet ds = Framework.Common.Sql.SqlHelper.ExecuteDataset(conn,
//                   System.Data.CommandType.Text,
//                   @"
//SELECT  * ,
//        周收益率 = ( SELECT TOP 1
//                        fUserTop_Week_IncomeRate
//                 FROM   VirtualInvestment_Stock_UserHistoryAllAssets
//                 WHERE  fUserID = t.userid
//                 ORDER BY fID DESC
//               )
//FROM    ( SELECT    * ,
//                    userid = ISNULL(( SELECT TOP 1
//                                                userid
//                                      FROM      [dbo].VirtualInvestment_Stock_HeXunJoinUserList
//                                      WHERE     hexunUserName = a.新飞狐注册账号
//                                                AND userType = 0
//                                    ), 0)
//          FROM      dbo.Export_XingYeStock a
//        ) t
//
//");

//            var excel = new Framework.ExcelExport.ExcelHelper();
//            excel.DataTableToExcel(ds.Tables[0]);
//            excel.SaveFile(@"c:\新飞狐投顾周收益率" + DateTime.Now.ToString("MMdd") + ".xls");
//        }
//    }
//}
